16. 缺失值-英国零售商数据处理

# ⚠️ 平台原始代码 - 请原样输入至教学平台（注释除外），平台才会判定答案正确
# 注：英国零售商data.csv数据文件本地没有，但平台已经内置
import pandas as pd  # 导入Pandas数据分析库
import numpy as np  # 导入NumPy数值计算库
path= '英国零售商data.csv'  # 设置path为"英国零售商data.csv"
df=pd.read_csv(path,dtype={'CustomerID':str,'InvoiceID':str})  # 从CSV文件读取数据存入df
# 剔除CustomerID的缺失数据
df.dropna(subset=['CustomerID'],inplace=True)
print(df.CustomerID.count())  # 输出非缺失值计数

import pandas as pd
import numpy as np

# 6家零售门店（A-F）的日销售数据
data = {
    'Store': ['A', 'B', 'C', 'D', 'E', 'F'],
    # 门店B和E的销售额缺失（系统故障或未营业）
    'Sales': [100, np.nan, 150, 200, np.nan, 180],
    # 门店C和F的利润缺失（成本数据未录入）
    'Profit': [10, 15, np.nan, 20, 25, np.nan],
    # 门店D的客户数缺失（客流计数器故障）
    'Customers': [50, 75, 100, np.nan, 80, 95]
}
df = pd.DataFrame(data)
print('原始数据:')
print(df)

# df.isnull()：返回布尔型数据框
# True表示该位置是缺失值，False表示不是
print('缺失值位置(True表示缺失):')
print(df.isnull())

# isnull()返回布尔值（True=1, False=0），sum()将True值相加
print('各列缺失值数量:')
print(df.isnull().sum())

# 计算每列缺失值占总行数的百分比
print('各列缺失值比例(%):')
print(df.isnull().mean() * 100)
print()

# 完整行数（无任何缺失的行）
print('完整行数(无缺失):', df.dropna().shape[0])
print('总行数:', df.shape[0])

import pandas as pd
import numpy as np

np.random.seed(42)
n_rows = 100
data_vis = pd.DataFrame({
    'A': np.random.rand(n_rows),
    'B': np.random.rand(n_rows),
    'C': np.random.rand(n_rows),
    'D': np.random.rand(n_rows)
})

# 场景1：变量A的10%随机缺失（MCAR）
mask_a = np.random.rand(n_rows) < 0.1
data_vis.loc[mask_a, 'A'] = np.nan

# 场景2：变量B的条件缺失（MAR：当A<0.5时B缺失）
data_vis.loc[data_vis['A'] < 0.5, 'B'] = np.nan

# 场景3：变量C的15%随机缺失
mask_c = np.random.rand(n_rows) < 0.15
data_vis.loc[mask_c, 'C'] = np.nan

# 场景4：变量D无缺失（对照组）
missing_matrix = data_vis.isnull()
print('各变量缺失值数量:')
print(missing_matrix.sum())

import pandas as pd
import numpy as np

data = {
    'Store': ['A', 'B', 'C', 'D', 'E'],
    'Sales': [100, np.nan, 150, 200, np.nan],
    'Profit': [10, 15, np.nan, 20, 25]
}
df = pd.DataFrame(data)
print('原始数据:')
print(df)
print()

# how='any'：只要行中有任何一个缺失值，就删除该行
df_drop_any = df.dropna(how='any')
print('删除含任何缺失值的行(how="any"):')
print(df_drop_any)

# how='all'：只有当行中所有值都缺失时，才删除
df_drop_all = df.dropna(how='all')
print('只删除全部缺失的行(how="all"):')
print(df_drop_all)

# subset=['Sales']：只检查Sales列是否缺失
df_drop_subset = df.dropna(subset=['Sales'])
print('只删除Sales列缺失的行:')
print(df_drop_subset)

# axis=1：按列操作（删除包含缺失值的列）
df_drop_cols = df.dropna(axis=1)
print('删除含缺失值的列:')
print(df_drop_cols)

import pandas as pd
import numpy as np

# 10天的价格序列，含3个缺失值
dates = pd.date_range('2024-01-01', periods=10)
prices = [100.5, np.nan, 101.2, 100.8, np.nan, np.nan, 102.5, 103.0, np.nan, 104.2]
df_prices = pd.DataFrame({'Date': dates, 'Price': prices})
print('原始价格数据:')
print(df_prices)

# 策略1：零填充（通常不推荐，价格不可能是0）
df_zero = df_prices.copy()
df_zero['Price'] = df_zero['Price'].fillna(0)
print('零填充(通常不推荐):')
print(df_zero['Price'].values)
print()

# 策略2：均值填充
mean_price = df_prices['Price'].mean()
df_mean = df_prices.copy()
df_mean['Price'] = df_mean['Price'].fillna(mean_price)
print(f'均值填充(均值={mean_price:.2f}):')
print(df_mean['Price'].values)

# 中位数比均值更稳健，不受极端值影响
median_price = df_prices['Price'].median()
df_median = df_prices.copy()
df_median['Price'] = df_median['Price'].fillna(median_price)
print(f'中位数填充(中位数={median_price:.2f}):')
print(df_median['Price'].values)

# method='ffill'：用前一个有效值填充
df_ffill = df_prices.copy()
df_ffill['Price'] = df_ffill['Price'].fillna(method='ffill')
print('前向填充(用前一个值填充):')
print(df_ffill[['Date', 'Price']])

# method='bfill'：用后一个有效值填充
df_bfill = df_prices.copy()
df_bfill['Price'] = df_bfill['Price'].fillna(method='bfill')
print('后向填充(用后一个值填充):')
print(df_bfill[['Date', 'Price']])

# interpolate(method='linear')：在两个已知点之间线性插值
df_linear = df_prices.copy()
df_linear['Price'] = df_linear['Price'].interpolate(method='linear')
print('线性插值:')
print(df_linear[['Date', 'Price']])

import pandas as pd
import numpy as np

np.random.seed(42)
dates = pd.date_range('2024-01-01', periods=50)
prices = 100 + np.random.randn(50).cumsum()
prices[10:15] = np.nan  # 模拟连续5天停牌
prices[30:32] = np.nan  # 模拟连续2天缺失
prices[45] = np.nan      # 模拟单日缺失

df = pd.DataFrame({'Date': dates, 'Price': prices})
df.set_index('Date', inplace=True)

# 策略1：时间插值（考虑日期间隔）
df_time = df.interpolate(method='time')

# 策略2：多项式插值（二阶）
df_poly = df.interpolate(method='polynomial', order=2)

print('时间插值结果(第10-15行):')
print(df_time.iloc[9:16])
print()
print('多项式插值结果(第10-15行):')
print(df_poly.iloc[9:16])

# 用前5天的平均价格填充缺失值
df_rolling = df.fillna(df.rolling(window=5, min_periods=1).mean())
print('滚动均值填充(窗口=5):')
print(df_rolling.iloc[9:16])

import pandas as pd
import numpy as np

stocks = pd.DataFrame({
    'Date': list(pd.date_range('2024-01-01', periods=5)) * 2,
    'Stock': ['A'] * 5 + ['B'] * 5,
    'Price': [100, 101, np.nan, 103, 104,
              50, np.nan, 52, 53, np.nan]
})
print('原始多股票数据:')
print(stocks)
print()

# 按股票代码分组，分别前向填充
stocks['Price'] = stocks.groupby('Stock')['Price'].fillna(method='ffill')
print('分组前向填充:')
print(stocks)

import pandas as pd
import numpy as np

# 模拟英国零售商数据（演示用）
np.random.seed(42)
n_records = 1000

df_retail = pd.DataFrame({
    'InvoiceID': [f'INV{i:06d}' for i in range(n_records)],
    'CustomerID': [f'CUST{np.random.randint(1, 201):05d}'
                   if np.random.rand() > 0.1 else np.nan
                   for _ in range(n_records)],
    'InvoiceDate': pd.date_range('2023-01-01', periods=n_records),
    'Quantity': np.random.randint(1, 100, n_records),
    'UnitPrice': np.random.uniform(1, 50, n_records),
    'Country': ['UK'] * n_records
})

# 人为引入缺失值（模拟真实场景）
df_retail.loc[np.random.choice(n_records, 50), 'CustomerID'] = np.nan
df_retail.loc[np.random.choice(n_records, 30), 'Quantity'] = np.nan
df_retail.loc[np.random.choice(n_records, 20), 'UnitPrice'] = np.nan

print('数据形状:', df_retail.shape)
print('\n缺失值统计:')
print(df_retail.isnull().sum())
print('\n缺失值比例(%):')
print(df_retail.isnull().mean() * 100)

# 复制数据，保留原始数据
df_clean = df_retail.copy()

# 决策：删除CustomerID缺失的行
# 理由：客户ID是关联交易的关键字段，无法合理推测
before_customer = len(df_clean)
df_clean.dropna(subset=['CustomerID'], inplace=True)
after_customer = len(df_clean)

print(f'CustomerID处理:')
print(f'  删除前: {before_customer} 行')
print(f'  删除后: {after_customer} 行')
print(f'  删除比例: {(before_customer - after_customer) / before_customer:.2%}')

# 决策：用中位数填充（稳健，不受异常值影响）
median_quantity = df_clean['Quantity'].median()
df_clean['Quantity'] = df_clean['Quantity'].fillna(median_quantity)

print(f'Quantity处理:')
print(f'  填充值(中位数): {median_quantity}')
print(f'  剩余缺失: {df_clean["Quantity"].isnull().sum()}')

# 决策：按CustomerID分组填充均值
# 理由：同一客户购买相似价位商品
df_clean['UnitPrice'] = df_clean.groupby('CustomerID')['UnitPrice'].transform(
    lambda x: x.fillna(x.mean())
)
# 剩余缺失用全局均值填充
df_clean['UnitPrice'] = df_clean['UnitPrice'].fillna(df_clean['UnitPrice'].mean())

print(f'UnitPrice处理:')
print(f'  分组均值填充')
print(f'  剩余缺失: {df_clean["UnitPrice"].isnull().sum()}')

print('=== 处理后数据质量 ===')
print(f'总行数: {len(df_clean)}')
print(f'完整行数: {df_clean.dropna().shape[0]}')
print(f'\n最终缺失值统计:')
print(df_clean.isnull().sum())

print('=== 数据质量报告 ===\n')
print(f'总记录数: {len(df_clean)}')
print(f'唯一客户数: {df_clean["CustomerID"].nunique()}')
print(f'时间范围: {df_clean["InvoiceDate"].min()} 至 {df_clean["InvoiceDate"].max()}')
print()

# 数值变量统计
print('数值变量统计:')
print(df_clean[['Quantity', 'UnitPrice']].describe())
print()

# 计算交易金额
df_clean['TotalAmount'] = df_clean['Quantity'] * df_clean['UnitPrice']
print(f'总交易额: £{df_clean["TotalAmount"].sum():,.2f}')
print(f'平均交易额: £{df_clean["TotalAmount"].mean():.2f}')

# 按客户分组统计
customer_stats = df_clean.groupby('CustomerID').agg({
    'TotalAmount': 'sum',
    'InvoiceID': 'count'
}).rename(columns={'InvoiceID': 'TransactionCount'})

print('交易金额前5名客户:')
print(customer_stats.nlargest(5, 'TotalAmount'))

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt

plt.rcParams['font.sans-serif'] = ['SimHei']
plt.rcParams['axes.unicode_minus'] = False

np.random.seed(42)
dates = pd.date_range('2024-01-01', periods=30)
prices_normal = 100 + np.random.randn(30).cumsum() * 0.5

# 创建停牌缺失
prices_suspended = prices_normal.copy()
prices_suspended[10:15] = np.nan  # 第一次停牌5天
prices_suspended[22:24] = np.nan  # 第二次停牌2天

df_stock = pd.DataFrame({
    'Date': dates,
    'Price': prices_suspended,
    'Price_Normal': prices_normal
})

print(f'缺失天数: {df_stock["Price"].isnull().sum()}')
print(f'缺失比例: {df_stock["Price"].isnull().mean():.2%}')

# 计算均方误差（MSE）
mse_ff = ((df_stock['ForwardFill'] - df_stock['Price_Normal']) ** 2).mean()
mse_lin = ((df_stock['LinearInterp'] - df_stock['Price_Normal']) ** 2).mean()

print('填充误差评估:')
print(f'前向填充 MSE: {mse_ff:.4f}')
print(f'线性插值 MSE: {mse_lin:.4f}')
print(f'\nMSE越小，填充效果越好。')